-- @owner: @li-xin12345
-- @date: 2022/08/05
-- @testpoint: 视图结合distinct+主键及order by column count>select column count * 0.75,order by后面乱序

--step1：创建测试表; expect:成功
drop table if exists t_ustore_sortdistinct_005;
create table t_ustore_sortdistinct_005(
     col_1 integer,
     col_2 bigint constraint t_ustore_sortdistinct_005_pk primary key,
     col_3 float8,
     col_4 decimal(12,6),
     col_5 bool,
     col_6 char(30),
     col_7 varchar2(50),
     col_8 varchar(30),
     col_9 interval day to second,
     col_10 timestamp,
     col_11 date,
     col_12 smalldatetime,
     col_13 timestamp without time zone,
     col_14 blob,
     col_15 clob,
     col_16 int[]
) with (storage_type=ustore);

--step2：创建序列与视图；except：成功
drop sequence if exists seq_ustore_sortdistinct;
create sequence seq_ustore_sortdistinct increment by 1 start with 10;
drop view if exists v_ustore_sortdistinct_005;
create view v_ustore_sortdistinct_005 as select * from t_ustore_sortdistinct_005;

--step3：插入数据；except：成功
insert into t_ustore_sortdistinct_005 values(1,seq_ustore_sortdistinct.nextval,   1+445.255,98*0.99,
true,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'), to_timestamp('2019-01-03 14:58:54.000000',
'yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),   lpad('10','12','01010')::blob,
rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_sortdistinct_005 values(1,seq_ustore_sortdistinct.nextval, 1+445.255,98*0.99,
true,lpad('abc','6','@'),lpad('abc','6','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  to_timestamp('2019-01-03 14:58:54.000000',
'yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    lpad('10','12','01010')::blob,
rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_sortdistinct_005 values(2,seq_ustore_sortdistinct.nextval,   1+445.255,98*0.99,
false,lpad('abc','6','@'),lpad('abc','5','b'),rpad('abc','5','e'),(interval '4 5:12:10.222' day to second(3)),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03 14:58:54.000000',
'yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),    lpad('10','12','01010')::blob,
rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_sortdistinct_005 values(3,seq_ustore_sortdistinct.nextval,   1+445.255,98*0.99,
false,lpad('abc','4','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:12:10.222' day to second(3)),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  to_timestamp('2019-01-03 14:58:54.000000',
'yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  lpad('10','12','01010')::blob,
rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_sortdistinct_005 values(3,seq_ustore_sortdistinct.nextval,   1+445.255,98*0.99,
false,lpad('abc','3','@'),lpad('abc','3','b'),rpad('abc','3','e'),(interval '4 5:07:10.222' day to second(3)),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  to_timestamp('2019-01-03 14:58:54.000000',
'yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  lpad('10','12','01010')::blob,
rpad('abc','9','a@123&^%djgk'),'{32,535,5645645,6767,76,67,56,48,979,978,7}');
insert into t_ustore_sortdistinct_005 values(4,seq_ustore_sortdistinct.nextval,   1+445.255,98*0.99,
true,lpad('abc','3','@'),lpad('abc','4','b'),rpad('abc','6','e'),(interval '4 5:07:10.222' day to second(3)),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  to_timestamp('2019-01-03 14:58:54.000000',
'yyyy-mm-dd hh24:mi:ss.ffffff'),to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),
to_timestamp('2019-01-03 14:58:54.000000','yyyy-mm-dd hh24:mi:ss.ffffff'),  lpad('10','12','01010')::blob,
rpad('abc','9','a@123&^%djgk'),'{43,535,5645645,6767,76,67,56,48,979,978,7}');

--step4：视图结合distinct+主键及order by column count>select column count * 0.75,order by后面乱序；except：成功
select distinct col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9 from v_ustore_sortdistinct_005
order by  col_9,col_1,col_4,col_3,col_2,col_5,col_8;
explain plan for select distinct col_1,col_2,col_3,col_4,col_5,col_6,col_7,col_8,col_9 from v_ustore_sortdistinct_005
order by col_9,col_1,col_4,col_3,col_2,col_5,col_8;

--step5：清理环境；except：成功
delete from plan_table;
drop sequence seq_ustore_sortdistinct;
drop view v_ustore_sortdistinct_005;
drop table t_ustore_sortdistinct_005;